if exists (select 1
            from  sysobjects
           where  id = object_id('view_bd_export_schedule')
            and   type = 'V')
   drop view view_bd_export_schedule
go

/*==============================================================*/
/* View: view_bd_export_schedule                                */
/*==============================================================*/
create view view_bd_export_schedule as
SELECT
	b.user_no,
	b.user_serial,
	g.deptId5 AS dept_serial,
	g.deptName5 AS dept_name,
	g.deptCode5 as deptCode,
	g.deptName4 AS deptName4,
	g.deptName1 AS deptName1,
	a.rq,
	a.year,
	a.month,
	b.user_lname AS user_name,
	SUBSTRING (
		isnull( d1, '' ),
		CHARINDEX(
			'|',
		isnull( d1, '' )) + 1,
		len(
		isnull( d1, '' ))) AS d1,
	SUBSTRING (
		isnull( d2, '' ),
		CHARINDEX(
			'|',
		isnull( d2, '' )) + 1,
		len(
		isnull( d2, '' ))) AS d2,
	SUBSTRING (
		isnull( d3, '' ),
		CHARINDEX(
			'|',
		isnull( d3, '' )) + 1,
		len(
		isnull( d3, '' ))) AS d3,
	SUBSTRING (
		isnull( d4, '' ),
		CHARINDEX(
			'|',
		isnull( d4, '' )) + 1,
		len(
		isnull( d4, '' ))) AS d4,
	SUBSTRING (
		isnull( d5, '' ),
		CHARINDEX(
			'|',
		isnull( d5, '' )) + 1,
		len(
		isnull( d5, '' ))) AS d5,
	SUBSTRING (
		isnull( d6, '' ),
		CHARINDEX(
			'|',
		isnull( d6, '' )) + 1,
		len(
		isnull( d6, '' ))) AS d6,
	SUBSTRING (
		isnull( d7, '' ),
		CHARINDEX(
			'|',
		isnull( d7, '' )) + 1,
		len(
		isnull( d7, '' ))) AS d7,
	SUBSTRING (
		isnull( d8, '' ),
		CHARINDEX(
			'|',
		isnull( d8, '' )) + 1,
		len(
		isnull( d8, '' ))) AS d8,
	SUBSTRING (
		isnull( d9, '' ),
		CHARINDEX(
			'|',
		isnull( d9, '' )) + 1,
		len(
		isnull( d9, '' ))) AS d9,
	SUBSTRING (
		isnull( d10, '' ),
		CHARINDEX(
			'|',
		isnull( d10, '' )) + 1,
		len(
		isnull( d10, '' ))) AS d10,
	SUBSTRING (
		isnull( d11, '' ),
		CHARINDEX(
			'|',
		isnull( d11, '' )) + 1,
		len(
		isnull( d11, '' ))) AS d11,
	SUBSTRING (
		isnull( d12, '' ),
		CHARINDEX(
			'|',
		isnull( d12, '' )) + 1,
		len(
		isnull( d12, '' ))) AS d12,
	SUBSTRING (
		isnull( d13, '' ),
		CHARINDEX(
			'|',
		isnull( d13, '' )) + 1,
		len(
		isnull( d13, '' ))) AS d13,
	SUBSTRING (
		isnull( d14, '' ),
		CHARINDEX(
			'|',
		isnull( d15, '' )) + 1,
		len(
		isnull( d14, '' ))) AS d14,
	SUBSTRING (
		isnull( d15, '' ),
		CHARINDEX(
			'|',
		isnull( d15, '' )) + 1,
		len(
		isnull( d15, '' ))) AS d15,
	SUBSTRING (
		isnull( d16, '' ),
		CHARINDEX(
			'|',
		isnull( d16, '' )) + 1,
		len(
		isnull( d16, '' ))) AS d16,
	SUBSTRING (
		isnull( d17, '' ),
		CHARINDEX(
			'|',
		isnull( d17, '' )) + 1,
		len(
		isnull( d17, '' ))) AS d17,
	SUBSTRING (
		isnull( d18, '' ),
		CHARINDEX(
			'|',
		isnull( d18, '' )) + 1,
		len(
		isnull( d18, '' ))) AS d18,
	SUBSTRING (
		isnull( d19, '' ),
		CHARINDEX(
			'|',
		isnull( d19, '' )) + 1,
		len(
		isnull( d19, '' ))) AS d19,
	SUBSTRING (
		isnull( d20, '' ),
		CHARINDEX(
			'|',
		isnull( d20, '' )) + 1,
		len(
		isnull( d20, '' ))) AS d20,
	SUBSTRING (
		isnull( d21, '' ),
		CHARINDEX(
			'|',
		isnull( d21, '' )) + 1,
		len(
		isnull( d21, '' ))) AS d21,
	SUBSTRING (
		isnull( d22, '' ),
		CHARINDEX(
			'|',
		isnull( d22, '' )) + 1,
		len(
		isnull( d22, '' ))) AS d22,
	SUBSTRING (
		isnull( d23, '' ),
		CHARINDEX(
			'|',
		isnull( d23, '' )) + 1,
		len(
		isnull( d23, '' ))) AS d23,
	SUBSTRING (
		isnull( d24, '' ),
		CHARINDEX(
			'|',
		isnull( d24, '' )) + 1,
		len(
		isnull( d24, '' ))) AS d24,
	SUBSTRING (
		isnull( d25, '' ),
		CHARINDEX(
			'|',
		isnull( d25, '' )) + 1,
		len(
		isnull( d25, '' ))) AS d25,
	SUBSTRING (
		isnull( d26, '' ),
		CHARINDEX(
			'|',
		isnull( d26, '' )) + 1,
		len(
		isnull( d26, '' ))) AS d26,
	SUBSTRING (
		isnull( d27, '' ),
		CHARINDEX(
			'|',
		isnull( d27, '' )) + 1,
		len(
		isnull( d27, '' ))) AS d27,
	SUBSTRING (
		isnull( d28, '' ),
		CHARINDEX(
			'|',
		isnull( d28, '' )) + 1,
		len(
		isnull( d28, '' ))) AS d28,
	SUBSTRING (
		isnull( d29, '' ),
		CHARINDEX(
			'|',
		isnull( d29, '' )) + 1,
		len(
		isnull( d29, '' ))) AS d29,
	SUBSTRING (
		isnull( d30, '' ),
		CHARINDEX(
			'|',
		isnull( d30, '' )) + 1,
		len(
		isnull( d30, '' ))) AS d30,
	SUBSTRING (
		isnull( d31, '' ),
		CHARINDEX(
			'|',
		isnull( d31, '' )) + 1,
		len(
		isnull( d31, '' ))) AS d31,
	isnull(c.user_lname,'系统排班') AS gly_name
FROM
	kt_paiba a
	INNER JOIN dt_user b ON a.user_serial= b.user_serial
	LEFT JOIN dt_user c ON a.gly_no= CONVERT ( VARCHAR ( 50 ), c.user_serial ) 
	LEFT JOIN view_bd_group g on g.deptId5 = b.user_dep
WHERE
	( b.dep_no IS NOT NULL ) 
	AND ( b.user_type<= 50 ) 
	OR (
	a.rq IS NULL)
go

if exists (select 1 from  sys.extended_properties
           where major_id = object_id('view_bd_export_schedule') and minor_id = 0)
begin
   declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description', 
   'user', @CurrentUser, 'view', 'view_bd_export_schedule'

end


select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '排班-排班导出视图',
   'user', @CurrentUser, 'view', 'view_bd_export_schedule'
go
